package com.example.service.serviceImple;

import com.example.mapper.one.GraduateMapper;
import com.example.mapper.one.TreeMapper;
import com.example.pojo.GraduateInfo;
import com.example.service.GraduateService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;

@Service
public class GraduateServiceImple implements GraduateService {

    @Resource
    private GraduateMapper graduateMapper;

    @Resource
    private TreeMapper treeMapper;

    @Override
    public Integer addGra(GraduateInfo graduateInfo) {
//        根据班级获取系别和专业
        int majorId = treeMapper.getAncestor((Integer) graduateInfo.getTeamId());
        int gradeId = treeMapper.getAncestor(majorId);
        graduateInfo.setMajorId(majorId);
        graduateInfo.setGradeId(gradeId);
        //        获取辅导员
        String assistantName = graduateMapper.getAssistantByGradeId(gradeId);
        if(assistantName == null){
            return -1;
        }
        else{
            try {
                int isAdd = graduateMapper.addGra(graduateInfo);
                return isAdd;
            }
            catch (Exception e){
                System.out.println(e);
                if(e instanceof DuplicateKeyException){
                    return -2;
                }else {
                    return -3;
                }
            }
        }
    }

    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean batchImport(String fileName, MultipartFile file,Integer teamId) throws Exception {
        boolean notNull = false;
        try {
            List<GraduateInfo> graList = new ArrayList<GraduateInfo>();
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                throw new Exception("上传文件格式不正确");
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);
            if(sheet!=null){
                notNull = true;
            }
            GraduateInfo graduateInfo;
            System.out.println(sheet.getPhysicalNumberOfRows());                  //3行  第一行表头    第二行数据    第三行空行

            for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) {          //从第二行开始取值
                Row row = sheet.getRow(r);
                if (row == null){
                    continue;
                }
                graduateInfo = new GraduateInfo();

                int sNo = (int)row.getCell(0).getNumericCellValue();
                if(sNo == 0){
                    break;
                }
                String sName = row.getCell(1).getStringCellValue();
                String instructorName = row.getCell(2).getStringCellValue();
                int instructorId = graduateMapper.queryInstructorId(instructorName);
                int workDirection = (int)row.getCell(3).getNumericCellValue();
                String city = row.getCell(4).getStringCellValue();
                int isCounterpart = (int)row.getCell(5).getNumericCellValue();
                String workUnit = row.getCell(6).getStringCellValue();
                String workPost = row.getCell(7).getStringCellValue();
                String workScale = row.getCell(8).getStringCellValue();
                int workMonth = (int)row.getCell(9).getNumericCellValue();
                String industry = row.getCell(10).getStringCellValue();
                int salary = (int)row.getCell(11).getNumericCellValue();
                row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
                String phone = row.getCell(12).getStringCellValue();

//            if(sNo == 0|| sName == null || instructorName == null || phone == null || assistant == null ) {
//                System.out.println("第" + (r + 1) + "行数据格式错误");
//            }
                graduateInfo.setsNo(sNo);
                graduateInfo.setsName(sName);
                graduateInfo.setInstructorId(instructorId);
                graduateInfo.setWorkDirection(workDirection);
                graduateInfo.setCity(city);
                graduateInfo.setIsCounterpart(isCounterpart);
                graduateInfo.setWorkUnit(workUnit);
                graduateInfo.setWorkPost(workPost);
                graduateInfo.setWorkScale(workScale);
                graduateInfo.setWorkMonth(workMonth);
                graduateInfo.setIndustry(industry);
                graduateInfo.setSalary(salary);
                graduateInfo.setPhone(phone);

                int majorId = treeMapper.getAncestor(teamId);
                int gradeId = treeMapper.getAncestor(majorId);
                graduateInfo.setGradeId(gradeId);
                graduateInfo.setMajorId(majorId);
                graduateInfo.setTeamId(teamId);
                System.out.println(sNo);
                graList.add(graduateInfo);
            }
            for (GraduateInfo graResord : graList) {
                int cnt = graduateMapper.getGraCount(graResord.getsNo());                        //判断数据是否存在
                /*插入*/
                if (cnt == 0) {
                    graduateMapper.addGra(graResord);
                } else {
                    /*修改*/
                    graduateMapper.updateGraById(graResord);
                }
            }
        } catch (Exception e){
            System.out.println(e);
        }
        return notNull;
    }

    @Override
    public Map getAllData(Integer gradeId) {
        Map resultMap = new HashMap();
        List<Integer> list1 = graduateMapper.queryJobOrient(gradeId);

        List<Map<String,Object>> list2 = graduateMapper.queryEmployNum(gradeId);
        List<Map<String,Object>> list3 = graduateMapper.queryMonthNum(gradeId);
        List list4 = new ArrayList();
        for(int i = 1;i < 13; i++){
            int countNum = 0;
            for(Map map:list3) {
                if (map.get("workMonth") != null) {
                    int month = (Integer) map.get("workMonth");
                    if (month == i) {
                        Long num = (Long) map.get("countNum");
                        countNum = num.intValue();
                    }
                }
            }
            list4.add(countNum);
        }
        List<Map<String,Object>> list5 = graduateMapper.querySalary(gradeId);
        List list6 = new ArrayList();
        String[] salaryArr = {"<1000","1000-1500","1500-2000","2000-2500","2500-3000","3000-3500","3500-4000","4000-4500","4500-5000",">5000"};
        for(int j = 0;j < salaryArr.length; j++){
            int listNum = 0;
            for(Map map:list5) {
                if (map.get("salaryArea") != null) {
                    String area = (String) map.get("salaryArea");
                    if (area.equals(salaryArr[j])) {
                        Long num = (Long) map.get("num");
                        listNum = num.intValue();
                    }
                }
                if(listNum != 0){
                    continue;
                }
            }
            list6.add(listNum);
        }

        DecimalFormat df = new DecimalFormat("######0.00");

        /*就业率*/
        Double employNum = graduateMapper.getEmployRate(gradeId);
        Double totalNum = 0.00;
        for(int k = 0; k < list1.size(); k++){
            totalNum += list1.get(k);
        }
        String employRate = df.format(employNum / totalNum * 100) + "%";

        /*专业对口率*/
        Double workNum = graduateMapper.getWorkNum(gradeId);
        Double counterpartNum = graduateMapper.getCounterpartRate(gradeId);
        String counterpartRate = df.format(counterpartNum / workNum * 100) + "%";

        List<Map<String,Object>> list7 = graduateMapper.getCityNum(gradeId);
        List cityList = new ArrayList();
        Map startCity = new HashMap();
        startCity.put("name","广州");

        /*迁徙城市*/
        for(Map map:list7) {
            List singerCity = new ArrayList();
            singerCity.add(startCity);
            Map endCity = new HashMap();
            endCity.put("name",map.get("city"));
            if((String) map.get("city") == "广州"){
                /*出发点的地标要大一点*/
                endCity.put("value",80);
            }
            else{
                endCity.put("value",50);
            }
            endCity.put("num",map.get("cityNum"));
            singerCity.add(endCity);
            cityList.add(singerCity);
        }

        List occupationList = new ArrayList();
        List<Map<String,Object>> list8 = graduateMapper.getIndustry(gradeId);
        List intershipMapArr = new ArrayList();
        for(Map map:list8) {
            if(map.get("name") != null){
                String industryName = (String)map.get("name");
                occupationList.add(industryName);
                List<Map<String,Object>> list9 = graduateMapper.getInternshipPosition(gradeId, industryName);
                intershipMapArr.addAll(list9);
                for(Map intershipMap:list9) {
                    occupationList.add((String)intershipMap.get("name"));
                }
            }
        }
        Map occupationMap = new HashMap();
        occupationMap.put("occupationList", occupationList);
        occupationMap.put("industryArr", list8);
        occupationMap.put("intershipMapArr", intershipMapArr);

        resultMap.put("code",200);
        resultMap.put("jobOrientList",list1);
        resultMap.put("employerList",list2);
        resultMap.put("monthList",list4);
        resultMap.put("salaryList",list6);
        resultMap.put("employRate",employRate);
        resultMap.put("counterpartRate",counterpartRate);
        resultMap.put("cityList",cityList);
        resultMap.put("occupationMap",occupationMap);
        return resultMap;
    }

    public static boolean isRowEmpty(Row row){
        System.out.println("读取excle有数据的列数为"+row.getLastCellNum());
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
                System.out.println("循环第几次："+(i+1));
                return false;
            }else {
                System.out.println("实际的excle有数据的列数为"+(i+1));
                break;
            }
        }
        return true;
    }
}
